﻿using CGXi_Sdk;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JES
{
   public class NPOLHelper
    {

        public void Open(string filePath)
        {
            // 根据扩展名创建Workbook
            IWorkbook workbook;
            string fileExt = Path.GetExtension(filePath).ToLower();

            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xlsx")
                    workbook = new XSSFWorkbook(fs); // 新版Excel
                else if (fileExt == ".xls")
                    workbook = new HSSFWorkbook(fs); // 旧版Excel
                else
                    throw new Exception("不支持的文件格式");
            }

            // 获取第一个工作表
            ISheet sheet = workbook.GetSheetAt(0);

            // 读取数据
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;

                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {
                    ICell cell = row.GetCell(j);
                    object value = GetCellValue(cell); // 自定义方法获取单元格值
                    Console.Write(value + "\t");
                }
                Console.WriteLine();
            }
        }


        public void Created(List<TestValue> valses,double hold)
        {
            // 创建工作簿
            IWorkbook workbook = new XSSFWorkbook(); // 创建xlsx格式
                                                     // IWorkbook workbook = new HSSFWorkbook(); // 创建xls格式

            // 创建工作表
            ISheet sheet = workbook.CreateSheet("Sheet1");

            // 创建表头行
            IRow headerRow = sheet.CreateRow(0);
            headerRow.CreateCell(0).SetCellValue("运行的轴");
            headerRow.CreateCell(1).SetCellValue("极值坐标");
            headerRow.CreateCell(2).SetCellValue("极值");
            headerRow.CreateCell(3).SetCellValue("实际最小值");
            headerRow.CreateCell(4).SetCellValue("平均最小值");
            headerRow.CreateCell(5).SetCellValue("筛选阈值:"+ hold);

            // 填充数据
            for (int i = 0; i < valses.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                row.CreateCell(0).SetCellValue(valses[i].Pos_Max.PosIndex);
                row.CreateCell(1).SetCellValue(valses[i].Pos_Max.Pos.ToString());
                row.CreateCell(2).SetCellValue(valses[i].Pos_Max.Value);
               
            }

            // 自动调整列宽
            for (int i = 0; i < 3; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            string path = DateTime.Now.ToString("HH_mm_ss_")+ "output.xlsx";
            // 保存文件
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }

        public static object GetCellValue(ICell cell)
        {
            if (cell == null) return null;

            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    // 判断是否为日期
                    if (DateUtil.IsCellDateFormatted(cell))
                        return cell.DateCellValue;
                    return cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    // 处理公式
                    try
                    {
                        // 返回计算公式的值
                        return cell.NumericCellValue;
                    }
                    catch
                    {
                        return cell.StringCellValue;
                    }
                case CellType.Error:
                    return cell.ErrorCellValue;
                default:
                    return null;
            }
        }
    }
}
